<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Statistic
 *
 * @author Vu
 */
class Model_StatisticMapper extends BaseMapper {
    //put your code here
    // $product_ids, $product_ids : string cac id. vd "3,4,90,117"
    public static function statisticproductby($from_date,$to_date, $product_ids, $groupproduct_ids){
        //var_dump(strlen($groupproduct_ids));exit();
         $entries = array();            
        if($from_date=="" && $to_date==""){
            $db = Zend_Db_Table::getDefaultAdapter();
            $select ="";
            if(strlen($groupproduct_ids)>0)
            {
                $select = 'select product_category.id,product_category.name,IFNULL(SUM(order_detail.quantity),0) as soluongban
                    from product 
                    left join order_detail  on product.id = order_detail.product_id
                    inner join product_category ON product.product_cat_id = product_category.id
                    where order_detail.order_status_id = 2 and product_category.id IN ('.$groupproduct_ids.')
                     and order_detail.agent_id > 1
                    group by product_category.id';
            }
            else
            {
                $andWhere = strlen($product_ids)>0?' and product.id IN ('.$product_ids.')':'and 1=1';
                
                $select = 'select product.id,product.name,IFNULL(SUM(order_detail.quantity),0) as soluongban
                    from product left join order_detail  on product.id = order_detail.product_id
                    where order_detail.order_status_id = 2 '.$andWhere.'
                         and order_detail.agent_id > 1
                    group by product.id';
            }
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor();           
            foreach ($rows as $row) {
                $arr=array();
                array_push($arr, $row->name, floor($row->soluongban));
                array_push($entries,$arr );
               
            }
        } else {
            $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
            $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
            $db = Zend_Db_Table::getDefaultAdapter();
            $select="";
            if(strlen($groupproduct_ids)>0)
            {
            $select = 'select product_category.id,product_category.name,IFNULL(SUM(t1.quantity),0) as soluongban
                    from product left join (select * from order_detail where order_detail.order_status_id = 2 
                    inner join product_category ON product.product_cat_id = product_category.id                                        
                    and UNIX_TIMESTAMP(order_created) between '.$fromdate.' and '.$todate.') as t1 on product.id = t1.product_id
                    and product_category.id IN ('.$groupproduct_ids.')
                         and order_detail.agent_id > 1
                    group by product_category.id';
            }
            else
            {
                $andWhere = strlen($product_ids)>0?' and product.id IN ('.$product_ids.')':'and 1=1';
                                
                $select = 'select product.id,product.name,IFNULL(SUM(t1.quantity),0) as soluongban
                    from product left join (select * from order_detail where order_detail.order_status_id = 2 
                    and UNIX_TIMESTAMP(order_created) between '.$fromdate.' and '.$todate.') as t1 on product.id = t1.product_id
                    '.$andWhere.' and order_detail.agent_id > 1
                    group by product.id';
            }
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor();
            foreach ($rows as $row) {
                $arr=array();
                array_push($arr, $row->name, floor($row->soluongban));
                array_push($entries,$arr );
            }
        }
            return $entries;

        
    }
    public static function statisticproductby1($from_date,$to_date, $product_ids, $groupproduct_ids){
        //var_dump(strlen($groupproduct_ids));exit();
         $entries = array();            
        if($from_date=="" && $to_date==""){
            $db = Zend_Db_Table::getDefaultAdapter();
            $select ="";
            if(strlen($groupproduct_ids)>0)
            {
                $select = 'select product_category.id,product_category.name,IFNULL(SUM(order_detail.quantity),0) as soluongban
                    from product 
                    left join order_detail  on product.id = order_detail.product_id
                    inner join product_category ON product.product_cat_id = product_category.id
                    where order_detail.order_status_id = 2 and product_category.id IN ('.$groupproduct_ids.')
                        and order_detail.agent_id = 1
                    group by product_category.id';
            }
            else
            {
                $andWhere = strlen($product_ids)>0?' and product.id IN ('.$product_ids.')':'and 1=1';
                
                $select = 'select product.id,product.name,IFNULL(SUM(order_detail.quantity),0) as soluongban
                    from product left join order_detail  on product.id = order_detail.product_id
                    where order_detail.order_status_id = 2 '.$andWhere.'
                           and order_detail.agent_id = 1
                    group by product.id';
            }
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor();           
            foreach ($rows as $row) {
                $arr=array();
                array_push($arr, $row->name, floor($row->soluongban));
                array_push($entries,$arr );
               
            }
        } else {
            $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
            $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
            $db = Zend_Db_Table::getDefaultAdapter();
            $select="";
            if(strlen($groupproduct_ids)>0)
            {
            $select = 'select product_category.id,product_category.name,IFNULL(SUM(t1.quantity),0) as soluongban
                    from product left join (select * from order_detail where order_detail.order_status_id = 2 
                    inner join product_category ON product.product_cat_id = product_category.id                                        
                    and UNIX_TIMESTAMP(order_created) between '.$fromdate.' and '.$todate.') as t1 on product.id = t1.product_id
                    and product_category.id IN ('.$groupproduct_ids.')
                           and order_detail.agent_id = 1
                    group by product_category.id';
            }
            else
            {
                $andWhere = strlen($product_ids)>0?' and product.id IN ('.$product_ids.')':'and 1=1';
                                
                $select = 'select product.id,product.name,IFNULL(SUM(t1.quantity),0) as soluongban
                    from product left join (select * from order_detail where order_detail.order_status_id = 2 
                    and UNIX_TIMESTAMP(order_created) between '.$fromdate.' and '.$todate.') as t1 on product.id = t1.product_id
                    '.$andWhere.'  and order_detail.agent_id = 1
                    group by product.id';
            }
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor();
            foreach ($rows as $row) {
                $arr=array();
                array_push($arr, $row->name, floor($row->soluongban));
                array_push($entries,$arr );
            }
        }
            return $entries;

        
    }
    //area
    public static function statisticproductareabuyby($from_date,$to_date){
        $entries = array();  
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select area.id, area.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join agent on order_detail.agent_id = agent.id
                inner join area on agent.area_id = area.id 
                where '.$conditions.'
                Group by area.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    INNER JOIN agent ON order_detail.agent_id = agent.id
                    INNER JOIN area ON agent.area_id = area.id
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE area.id ='.$row->id.' and '.$conditions.'                   
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    public static function statisticproductareasaleby($from_date,$to_date){
        $entries = array(); 
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select area.id, area.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join `order` on `order`.order_no = order_detail.order_id
                inner join agent on `order`.store_id = agent.id
                inner join area on agent.area_id = area.id 
                where '.$conditions.'
                Group by area.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    inner join `order` ON `order`.order_no = order_detail.order_id
                    INNER JOIN agent ON `order`.store_id = agent.id
                    INNER JOIN area ON agent.area_id = area.id
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE area.id ='.$row->id.'  and '.$conditions.'                  
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    //province
    public static function statisticproductprovincebuyby($from_date,$to_date, $area_id){
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select province.id, province.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join agent on order_detail.agent_id = agent.id
                inner join province on agent.province_id = province.id  
                where '.$conditions.'
                Group by province.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    INNER JOIN agent ON order_detail.agent_id = agent.id
                    INNER JOIN province ON agent.province_id = province.id
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE province.id ='.$row->id.' and '.$conditions.'                   
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    public static function statisticproductprovincesaleby($from_date,$to_date, $area_id){
        $entries = array();   
        $fromdate = 0;
        $todate = 0;
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select province.id, province.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join `order` on `order`.order_no = order_detail.order_id
                inner join agent on `order`.store_id = agent.id
                inner join province on agent.province_id = province.id  
                where '.$conditions.'
                Group by province.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    inner join `order` ON `order`.order_no = order_detail.order_id
                    INNER JOIN agent ON `order`.store_id = agent.id
                    INNER JOIN province ON agent.province_id = province.id
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE province.id ='.$row->id.'  and '.$conditions.'                  
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    //district
    public static function statisticproductdistrictbuyby($from_date,$to_date, $area_id, $province_id){
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        if($province_id!=0 && $province_id!='') $conditions = $conditions.' and agent.province_id = '.$province_id;
        
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select district.id, district.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join agent on order_detail.agent_id = agent.id
                inner join district on agent.district_id = district.id 
                where '.$conditions.'
                Group by district.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    INNER JOIN agent ON order_detail.agent_id = agent.id
                    INNER JOIN district ON agent.district_id = district.id
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE district.id ='.$row->id.' and '.$conditions.'                   
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    public static function statisticproductdistrictsaleby($from_date,$to_date, $area_id, $province_id){
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        if($province_id!=0 && $province_id!='') $conditions = $conditions.' and agent.province_id = '.$province_id;
        
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
       
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select district.id, district.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join `order` on `order`.order_no = order_detail.order_id
                inner join agent on `order`.store_id = agent.id
                inner join district on agent.district_id = district.id     
                where '.$conditions.'
                Group by district.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    inner join `order` ON `order`.order_no = order_detail.order_id
                    INNER JOIN agent ON `order`.store_id = agent.id
                    INNER JOIN district ON agent.district_id = district.id
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE district.id ='.$row->id.'  and '.$conditions.'                  
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    //agent    
    public static function statisticagentcolumnby($from_date,$to_date, $agent_id, $product_id)
    {
        $series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        //if($province_id!=0 && $province_id!='') $conditions = $conditions.' and agent.province_id = '.$province_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from agent where id IN ('.$agent_id.') order by id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //$color = -1;
            $province= array();
            foreach ($rows as $row) {
                array_push($province, $row->name);
            }
                $select1 = 'SELECT * FROM product where id IN ('.$product_id.')';
//                if($area_id > 0){
//                $select1 = 'SELECT * FROM province 
//                    inner join area_province_category on area_province_category.province_id= province.id
//                    where area_province_category.area_id = '.$area_id;
//                }
                
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //$category1 = array();
                $seri=array();
                foreach ($rows1 as $row1) {
                    $soluong = array();
                    $select2 = 'SELECT agent.id,agent.name,ifnull(t1.soluong,0) as soluong FROM agent
                                left join
                                (SELECT agent.id, agent.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN province ON agent.province_id = province.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE product.id ='.$row1->id.' and '.$conditions.'                                                         
                                                    GROUP BY agent.id) as t1 on t1.id=agent.id
                                WHERE agent.id IN ('.$agent_id.') 
                                order by agent.id 
                                                    ';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2){
                                array_push($soluong, floor($row2->soluong));
                            }
                    $seri=  array_merge($seri,array('name'=>$row1->name,'data'=>$soluong))  ;      
                     array_push($series, $seri);
                }
            
            $entries = array_merge($entries,array('categories'=>$province,'series'=>$series));
            return $entries;
    }
    public static function statisticagentpercentbuyby($from_date,$to_date, $agent_id, $product_id){
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
    
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        $conditions = $conditions.' and order_detail.product_id IN ('.$product_id.') and agent.id IN ('.$agent_id.')';
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select agent.id, agent.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join agent on order_detail.agent_id = agent.id                
                where '.$conditions.'                
                Group by agent.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    INNER JOIN agent ON order_detail.agent_id = agent.id                    
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE agent.id ='.$row->id.' and '.$conditions.'                         
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    public static function statisticagentpercentsaleby($from_date,$to_date, $agent_id, $product_id){
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        $conditions = $conditions.' and order_detail.product_id IN ('.$product_id.') and agent.id IN ('.$agent_id.')';
               
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select agent.id, agent.name, IFNULL(SUM(order_detail.quantity),0) as soluong
                from order_detail
                inner join `order` on `order`.order_no = order_detail.order_id
                inner join agent on `order`.store_id = agent.id                  
                where '.$conditions.'
                Group by agent.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            $color = -1;
            foreach ($rows as $row) {
                $color++;
                $select1 = 'SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                    FROM order_detail
                    inner join `order` ON `order`.order_no = order_detail.order_id
                    INNER JOIN agent ON `order`.store_id = agent.id                    
                    INNER JOIN product ON order_detail.product_id = product.id
                    WHERE agent.id ='.$row->id.'  and '.$conditions.'                  
                    GROUP BY product.id
                    ';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                $category1 = array();
                $data1=array();
                foreach ($rows1 as $row1) {
                    array_push($category1, $row1->name);
                    array_push($data1, floor($row1->soluong));
                }
                $drilldown = array();
                $drilldown = array_merge($drilldown, array( 'name' => 'abc', 'categories'=>$category1, 'data'=>$data1, 'color' => $color));
                $arr = array();
                $arr = array_merge($arr, array('name' => $row->name,'y' => floor($row->soluong),'color' => $color, 'drilldown' => $drilldown));
                array_push($entries,$arr );
               
            }
        
            return $entries;
    }
    public static function statisticagentgridby($from_date,$to_date, $agent_id, $product_id)
    {
        //$series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
       // if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        $conditions = $conditions.' and order_detail.product_id IN ('.$product_id.') and agent.id IN ('.$agent_id.')';
                
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //get area
            $area= array();
            foreach ($rows as $row) {
                array_push($area, $row->name);
            }
                $select1 = 'SELECT * FROM agent';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //get ten sp va so luong push series
                //$seri=array();
                
                    $select2 = 'SELECT product.id,product.name as product_name,agent.name as agent_name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN area ON agent.area_id = area.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE '.$conditions.'                 
                                                    GROUP BY agent.id, product.id
                                                    ';
                  
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                 array_push($entries, $row2);
                            }

                
            return $entries;
    }
    //truyen
    //thong ke theo tinh thanh
    public static function test($from_date,$to_date,$area_id)
    {
        $series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product order by product.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //$color = -1;
            $province= array();
            foreach ($rows as $row) {
                array_push($province, $row->name);
            }
                $select1 = 'SELECT * FROM province';
                if($area_id > 0){
                $select1 = 'SELECT * FROM province 
                    inner join area_province_category on area_province_category.province_id= province.id
                    where area_province_category.area_id = '.$area_id;
                }
                
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //$category1 = array();
                $seri=array();
                foreach ($rows1 as $row1) {
                    $soluong = array();
                    $select2 = 'SELECT product.id,product.name,ifnull(t1.soluong,0) as soluong FROM product
                                left join
                                (SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN province ON agent.province_id = province.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE province.id ='.$row1->id.' and '.$conditions.'                 
                                                    GROUP BY product.id) as t1 on t1.id=product.id
                                                    order by product.id';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                array_push($soluong, floor($row2->soluong));

                            }
                    $seri=  array_merge($seri,array('name'=>$row1->name,'data'=>$soluong))  ;      
                     array_push($series, $seri);
                }
            
                
               
               
            
            $entries = array_merge($entries,array('categories'=>$province,'series'=>$series));
            return $entries;
    }
    
    // thong ke theo khu vuc
    public static function test1($from_date,$to_date)
    {
        $series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
       // if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product order by product.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //get area
            $area= array();
            foreach ($rows as $row) {
                array_push($area, $row->name);
            }
                $select1 = 'SELECT * FROM area';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //get ten sp va so luong push series
                $seri=array();
                foreach ($rows1 as $row1) {
                    $soluong = array();
                    $select2 = 'SELECT product.id,product.name,ifnull(t1.soluong,0) as soluong FROM product
                                left join
                                (SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN area ON agent.area_id = area.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE area.id ='.$row1->id.' and '.$conditions.'                 
                                                    GROUP BY product.id) as t1 on t1.id=product.id
                                                    order by product.id';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                array_push($soluong, floor($row2->soluong));

                            }
                    $seri=  array_merge($seri,array('name'=>$row1->name,'data'=>$soluong))  ;      
                     array_push($series, $seri);
                }
            $entries = array_merge($entries,array('categories'=>$area,'series'=>$series));
            return $entries;
    }
    //District
    public static function test2($from_date,$to_date,$area_id,$province_id)
    {
        $series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        if($province_id!=0 && $province_id!='') $conditions = $conditions.' and agent.province_id = '.$province_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product order by product.id';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //$color = -1;
            $district= array();
            foreach ($rows as $row) {
                array_push($district, $row->name);
            }
                $select1 = 'SELECT * FROM district ';
                if($area_id > 0 && $province_id >0){
                $select1 = 'SELECT * FROM district
                    where province_id = '.$province_id;
                }
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //$category1 = array();
                $seri=array();
                foreach ($rows1 as $row1) {
                    $soluong = array();
                    $select2 = 'SELECT product.id,product.name,ifnull(t1.soluong,0) as soluong FROM product
                                left join
                                (SELECT product.id, product.name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN district ON agent.district_id = district.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE district.id ='.$row1->id.' and '.$conditions.'                 
                                                    GROUP BY product.id) as t1 on t1.id=product.id
                                                    order by product.id';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                array_push($soluong, floor($row2->soluong));

                            }
                    $seri=  array_merge($seri,array('name'=>$row1->name,'data'=>$soluong))  ;      
                     array_push($series, $seri);
                }
            $entries = array_merge($entries,array('categories'=>$district,'series'=>$series));
            return $entries;
    }
    //thong ke theo khu vuc kieu datagrids
    public static function test4($from_date,$to_date)
    {
        //$series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
       // if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //get area
            $area= array();
            foreach ($rows as $row) {
                array_push($area, $row->name);
            }
                $select1 = 'SELECT * FROM area';
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //get ten sp va so luong push series
                //$seri=array();
                foreach ($rows1 as $row1) {
                   // $soluong = array();
                    $select2 = 'SELECT product.id,product.name as product_name,area.name as area_name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN area ON agent.area_id = area.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE area.id ='.$row1->id.' and '.$conditions.'                 
                                                    GROUP BY product.id
                                                    ';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                 array_push($entries, $row2);
                            }

                }
            return $entries;
    }
    //thong ke theo quan huyen dang bang
    public static function test5($from_date,$to_date,$area_id,$province_id)
    {
        //$series=array();
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        if($province_id!=0 && $province_id!='') $conditions = $conditions.' and agent.province_id = '.$province_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //$color = -1;
            $district= array();
            foreach ($rows as $row) {
                array_push($district, $row->name);
            }
                $select1 = 'SELECT * FROM district ';
                if($area_id > 0 && $province_id >0){
                $select1 = 'SELECT * FROM district
                    where province_id = '.$province_id;
                }
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                //$category1 = array();
                $seri=array();
                foreach ($rows1 as $row1) {
                    //$soluong = array();
                    $select2 = 'SELECT product.id, product.name as product_name,district.name as district_name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN district ON agent.district_id = district.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE district.id ='.$row1->id.' and '.$conditions.'                 
                                                    GROUP BY product.id
                                                    ';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                array_push($entries, $row2);

                            }
                    //$seri=  array_merge($seri,array('name'=>$row1->name,'data'=>$soluong))  ;      
                     //array_push($series, $seri);
                }
           // $entries = array_merge($entries,array('categories'=>$district,'series'=>$series));
            return $entries;
    }
     //thong ke theo tinh thanh dang cot
    public static function test6($from_date,$to_date,$area_id)
    {
       
        $entries = array(); 
        $fromdate = 0;
        $todate = 0;
        
        $conditions = ' 1=1 and order_detail.order_status_id = 2';
        if($from_date=="" ) $fromdate = strtotime('01-01-1990'); else $fromdate = strtotime(GlobalLib::toMysqlDateString($from_date));
        if($to_date=="" ) $todate = strtotime(date('d-m-Y')); else $todate = strtotime(GlobalLib::toMysqlDateString($to_date));
        if($area_id!=0 && $area_id!='') $conditions = $conditions.' and agent.area_id = '.$area_id;
        $conditions = $conditions.' and (UNIX_TIMESTAMP(order_detail.order_created) between '.$fromdate.' and '.$todate.')';
        
        
            $db = Zend_Db_Table::getDefaultAdapter();
            $select = 'select * from product';
            $stmt = $db->query($select);
            $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
            $stmt->closeCursor(); 
            //$color = -1;
            $province= array();
            foreach ($rows as $row) {
                array_push($province, $row->name);
            }
                $select1 = 'SELECT * FROM province';
                if($area_id > 0){
                $select1 = 'SELECT * FROM province 
                    inner join area_province_category on area_province_category.province_id= province.id
                    where area_province_category.area_id = '.$area_id;
                }
                
                $stmt1 = $db->query($select1);
                $rows1 = $stmt1->fetchAll(PDO::FETCH_CLASS);
                $stmt1->closeCursor();
                
                
                foreach ($rows1 as $row1) {
                    $soluong = array();
                    $select2 = 'SELECT product.id, product.name as product_name,province.name as province_name, IFNULL( SUM( order_detail.quantity ) , 0 ) AS soluong
                                                    FROM order_detail
                                                    INNER JOIN agent ON order_detail.agent_id = agent.id
                                                    INNER JOIN province ON agent.province_id = province.id
                                                    INNER JOIN product ON order_detail.product_id = product.id
                                                    WHERE province.id ='.$row1->id.' and '.$conditions.'                 
                                                    GROUP BY product.id
                                                    ';
                            $stmt2 = $db->query($select2);
                            $rows2 = $stmt2->fetchAll(PDO::FETCH_CLASS);
                            $stmt2->closeCursor();
                            foreach ($rows2 as $row2)
                             {
                                array_push($entries, $row2);

                            }
                   
                }
            
            return $entries;
    }
}
